﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Smo;
namespace PropertyManagement.Core {
    public class DatabaseManage {

        //public void ExecSqlScriptFile(string scriptFile) {
        //   // string sqlQuery = "osql.exe -U {1} -P {2} -S {0}  -i {3}";
        //    //string strRst = ExeCommand(string.Format(sqlQuery,GetServerName(),GetUserName(),GetPassword(),scriptFile));
        //    //Console.WriteLine(strRst);
        //    //Console.ReadLine( );
        //    ExecSqlScriptFile(scriptFile);
        //} 

        public void InitDatabase() {
            ExeSqlScriptFile("Resource/sql-script.sql");
        }

        public bool Backup(string filepath) {
            var sql = string.Format("backup database {1} to disk='{0}'", filepath,GetDatabaseName());
            try {
                SqlServerHelper.ExecuteNonQuery(SqlServerHelper.ConnectionString, CommandType.Text, sql);
                return true;
            }
            catch {
                return false;
            }
        }

        public bool Restore(string filepath) {
            var sql = string.Format("use master ; restore database {1} from disk='{0}'; use {1};", filepath, GetDatabaseName( ));
            try {
                SqlServerHelper.ExecuteNonQuery(SqlServerHelper.ConnectionString, CommandType.Text, sql);
                return true;
            }
            catch {
                return false;
            }
        }

        private string GetDatabaseName( ) {
            var i = connectionStr.IndexOf("Database =");
            var i2 = connectionStr.IndexOf(";", i);
            return connectionStr.Substring(i + 10, i2 - i - 10);
        }



        private static string connectionStr = SqlServerHelper.ConnectionString;

        private string GetServerName() {
            var i = connectionStr.IndexOf("Server =");
            var i2 = connectionStr.IndexOf(";", i );
            return connectionStr.Substring(i + 8, i2 -i-8);
        }

        private string GetUserName( ) {
            var i = connectionStr.IndexOf("User ID=");
            var i2 = connectionStr.IndexOf(";", i );
            return connectionStr.Substring(i + 8, i2 - i-8 );
        }

        private string GetPassword( ) {
            var i = connectionStr.IndexOf("Password=");
            var i2 = connectionStr.IndexOf(";", i );
            return connectionStr.Substring(i + 9, i2 - i-9 );
        }

        private static string ExeCommand(string commandText) {
            Process p = new Process( );
            p.StartInfo.FileName = "cmd.exe";
            p.StartInfo.UseShellExecute = false;
            p.StartInfo.RedirectStandardInput = true;
            p.StartInfo.RedirectStandardOutput = true;
            p.StartInfo.RedirectStandardError = true;
            p.StartInfo.CreateNoWindow = true;
            string strOutput = null;
            try {
                p.Start( );
                p.StandardInput.WriteLine(commandText);
                p.StandardInput.WriteLine("exit");
                strOutput = p.StandardOutput.ReadToEnd( );
                p.WaitForExit( );
                p.Close( );
            } catch (Exception e) {
                strOutput = e.Message;
            }
            return strOutput;
        }


        private static void ExeSqlScriptFile(string scriptFile) {
            if (File.Exists(scriptFile)) {
                var file = new FileInfo(scriptFile);
                var script = file.OpenText().ReadToEnd();
               
                var conn = new SqlConnection(connectionStr);
                var server = new Server(new ServerConnection(conn));
                int i = server.ConnectionContext.ExecuteNonQuery(script);
                
                
            }
        }
    }
}
